Defining NULL and NOT NULL Constraints in MySQL – Complete Notes

In MySQL, NULL and NOT NULL determine whether a column can store a missing or unknown value. These constraints are important for designing reliable database tables, maintaining data accuracy, and ensuring that required information is provided.

MySQL Training Course: JustAcademy MySQL Training

Course Demo: Register for MySQL Course Demo

1. Learning Objectives

After completing this topic, learners will be able to:

• Understand the meaning of NULL in MySQL.
• Differentiate between NULL, zero, and an empty string.
• Define nullable and non-nullable columns.
• Apply NULL and NOT NULL while creating tables.
• Insert, update, and retrieve records containing NULL values.
• Use IS NULL and IS NOT NULL in SQL queries.
• Modify existing columns to allow or disallow NULL.
• Identify common errors and follow best practices for data integrity.

2. What Is NULL in MySQL?

NULL is a special value that represents missing, unknown, or unavailable information. It does not represent an ordinary value such as a number or text.

For example, a student's date of birth may be unknown when the student registers. In that situation, the date_of_birth column can store NULL if the column permits it.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    date_of_birth DATE NULL
);

In this example:

• student_id uniquely identifies each student.
• student_name is required and cannot be NULL.
• date_of_birth can contain a date or NULL.

Important: NULL does not mean that a value is zero, blank, or equal to another NULL. It means that the value is absent or unknown.

3. What Is the NULL Constraint?

In a column definition, NULL explicitly permits the column to store a NULL value. In MySQL, columns are nullable by default when neither NULL nor NOT NULL is specified, subject to other rules such as primary-key requirements.

Syntax

column_name data_type NULL;

Example

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20) NULL,
    alternate_email VARCHAR(150) NULL
);

The phone_number and alternate_email columns permit missing values. This is useful when the information is optional or is not available when the record is created.

Inserting NULL Values

INSERT INTO employees (
    employee_id,
    employee_name,
    phone_number,
    alternate_email
)
VALUES (
    101,
    'Rahul Sharma',
    NULL,
    NULL
);

The statement inserts an employee without a phone number or alternate email. It succeeds because both columns allow NULL.

4. What Is the NOT NULL Constraint?

The NOT NULL constraint ensures that a column cannot store a NULL value. It is used for information that must be present for a record to be meaningful.

Syntax

column_name data_type NOT NULL;

Example

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    city VARCHAR(80) NULL
);

In this table, the customer name and email cannot be NULL, but the city can be unknown or unavailable.

Valid INSERT Statement

INSERT INTO customers (
    customer_id,
    customer_name,
    email,
    city
)
VALUES (
    1,
    'Priya Patel',
    '[email protected]',
    NULL
);

This statement is valid because the required fields have values. The optional city field is allowed to contain NULL.

Invalid INSERT Statement

INSERT INTO customers (
    customer_id,
    customer_name,
    email,
    city
)
VALUES (
    2,
    NULL,
    '[email protected]',
    'Mumbai'
);

This statement violates the NOT NULL constraint on customer_name. In normal strict SQL mode, MySQL rejects the statement rather than accepting a missing required value.

5. Difference Between NULL and NOT NULL

6. Difference Between NULL, Zero, and Empty String

One of the most important concepts in MySQL is that NULL, 0, and '' have different meanings.

Example

SELECT
    NULL IS NULL AS null_check,
    0 IS NULL AS zero_check,
    '' IS NULL AS empty_string_check;

Expected result:

In MySQL, 1 represents true and 0 represents false in this result. Both zero and an empty string are actual values, not NULL.

7. Defining NULL and NOT NULL During Table Creation

You can specify whether individual columns allow missing values when creating a table using CREATE TABLE.

Example

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50) NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(150) NOT NULL,
    phone VARCHAR(20) NULL,
    date_of_birth DATE NULL
);

Explanation:

• student_id is the primary key and cannot be NULL.
• first_name and last_name are mandatory.
• middle_name is optional.
• email is required.
• phone and date_of_birth can be missing.

This approach makes the table's data requirements clear from the beginning.

8. Inserting Records with NULL Values

When inserting records, you can explicitly supply NULL for a nullable column.

INSERT INTO students (
    first_name,
    middle_name,
    last_name,
    email,
    phone,
    date_of_birth
)
VALUES (
    'Rahul',
    NULL,
    'Sharma',
    '[email protected]',
    NULL,
    NULL
);

In this example, the student's middle name, phone number, and date of birth are not available.

Omitting Nullable Columns

INSERT INTO students (
    first_name,
    last_name,
    email
)
VALUES (
    'Priya',
    'Patel',
    '[email protected]'
);

If the omitted nullable columns have no other applicable default, MySQL assigns NULL to them. This differs from explicitly supplying an empty string or zero.

Important Note About Defaults

If a column has a declared default value, omitting it from an INSERT statement uses that default. Explicitly supplying NULL is a different operation and does not normally mean “use the default.”

9. Finding NULL Values with IS NULL

The IS NULL operator checks whether a column contains a NULL value.

Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

Example

SELECT *
FROM students
WHERE phone IS NULL;

This query returns students whose phone number is missing.

Find Students Without a Date of Birth

SELECT student_id, first_name, last_name
FROM students
WHERE date_of_birth IS NULL;

This query can help identify records that may need additional information.

10. Finding Non-NULL Values with IS NOT NULL

The IS NOT NULL operator returns rows in which the specified column contains a non-NULL value.

Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Example

SELECT *
FROM students
WHERE phone IS NOT NULL;

This query returns students who have a phone value stored in the database.

Find Students with a Recorded Date of Birth

SELECT student_id, first_name, last_name, date_of_birth
FROM students
WHERE date_of_birth IS NOT NULL;

Use this operator when filtering for values that have been provided or recorded.

11. Why You Should Not Use = NULL

In SQL, NULL represents an unknown or missing value. An ordinary equality comparison with NULL does not return true, even when comparing NULL with itself.

Incorrect Query

SELECT *
FROM students
WHERE phone = NULL;

This query does not correctly identify rows with a NULL phone number.

Correct Query

SELECT *
FROM students
WHERE phone IS NULL;

Similarly, use IS NOT NULL instead of <> NULL or != NULL.

12. Updating NULL Values

The UPDATE statement can change a column from NULL to an actual value, provided the new value satisfies the column's constraints.

Example

UPDATE students
SET phone = '9876543210'
WHERE student_id = 1;

This statement adds a phone number for the student with ID 1.

Setting a Value to NULL

UPDATE students
SET phone = NULL
WHERE student_id = 1;

This operation works only if the phone column permits NULL. If the column is defined as NOT NULL, the update is rejected in normal strict SQL mode.

Best practice: Always use a suitable WHERE clause when updating records to avoid changing unintended rows.

13. Adding NOT NULL to an Existing Column

You can modify an existing column using ALTER TABLE and MODIFY COLUMN. Before adding NOT NULL, check whether the column already contains NULL values.

Step 1: Check for NULL Values

SELECT *
FROM students
WHERE phone IS NULL;

Step 2: Replace or Resolve Missing Values

If the business rules permit a placeholder, update the missing values appropriately. Do not invent real contact information merely to satisfy a constraint.

UPDATE students
SET phone = 'Not provided'
WHERE phone IS NULL;

Important: This example requires a suitable text column and an application policy that accepts the placeholder. If the column is numeric or the placeholder is not a valid business value, use a different migration strategy.

Step 3: Modify the Column

ALTER TABLE students
MODIFY COLUMN phone VARCHAR(20) NOT NULL;

This changes the column definition to disallow NULL. The full column definition must preserve the intended data type and other relevant attributes, such as DEFAULT or COMMENT, where applicable.

14. Removing NOT NULL from an Existing Column

If a field becomes optional, you can modify its definition to allow NULL.

Example

ALTER TABLE students
MODIFY COLUMN phone VARCHAR(20) NULL;

After this modification, the phone column can store NULL values.

Note: Review applications, reports, and database constraints that depend on the column before changing its nullability.

15. Combining NULL and NOT NULL with DEFAULT

The DEFAULT attribute supplies a value when an insert statement omits a column. It works alongside nullability, but the two concepts are different.

Example: Nullable Column with a Default

CREATE TABLE support_tickets (
    ticket_id INT AUTO_INCREMENT PRIMARY KEY,
    subject VARCHAR(150) NOT NULL,
    description TEXT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Open'
);

In this example:

• subject must have a value.
• description may be NULL.
• status cannot be NULL and defaults to Open when omitted.

Insert Without Specifying the Status

INSERT INTO support_tickets (subject, description)
VALUES ('Login issue', NULL);

MySQL uses the default status Open because the insert statement omits that column.

16. Using NULL in Conditions and Calculations

Expressions involving NULL can produce NULL or an unknown logical result. This affects comparisons, calculations, and filtering.

NULL in Arithmetic

SELECT 100 + NULL AS result;

The result is NULL because the missing value prevents MySQL from calculating a definite sum.

Using IFNULL()

The IFNULL() function returns an alternative value when the expression is NULL.

SELECT
    student_id,
    IFNULL(phone, 'Not available') AS phone_display
FROM students;

This query displays Not available when the phone value is NULL. It changes the query result, not the stored data.

Using COALESCE()

COALESCE() returns the first non-NULL expression in its argument list.

SELECT
    student_id,
    COALESCE(phone, email, 'No contact details') AS contact_details
FROM students;

The query uses the phone number if available, otherwise the email, and finally the fallback text.

17. NULL and Aggregate Functions

MySQL aggregate functions generally ignore NULL values. One important exception is COUNT(*), which counts rows regardless of whether individual columns contain NULL.

Example

SELECT
    COUNT(*) AS total_students,
    COUNT(phone) AS students_with_phone
FROM students;

Explanation:

• COUNT(*) counts all rows.
• COUNT(phone) counts rows where phone is not NULL.

Similarly, SUM(), AVG(), MIN(), and MAX() ignore NULL inputs. If an aggregate has no applicable non-NULL values, its result may be NULL.

18. NULL Values and Primary Keys

A primary key cannot contain NULL values. It uniquely identifies each row in a table.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    description TEXT NULL
);

The product_id column cannot be NULL, while the description is optional. MySQL automatically requires primary-key columns to be non-nullable.

19. NULL Values and Foreign Keys

A foreign-key column can allow NULL if the relationship is optional. A non-NULL foreign-key value must satisfy the foreign-key rules.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT NULL,
    CONSTRAINT fk_employee_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

Here, an employee may have no assigned department because department_id allows NULL. If a department ID is provided, it must refer to an existing department.

Important: If a foreign key uses ON DELETE SET NULL, the referencing column must allow NULL. A NOT NULL definition would conflict with that action.

20. Viewing Column Nullability

Use DESCRIBE or SHOW COLUMNS to check whether a column accepts NULL.

DESCRIBE students;

The result contains a Null column. YES means the column can accept NULL; NO means it cannot.

SHOW COLUMNS FROM students;

You can also inspect the complete table definition:

SHOW CREATE TABLE students;

These commands help verify that the table structure matches the intended design.

21. Common Errors and Their Solutions

22. Best Practices for NULL and NOT NULL

• Use NOT NULL for fields that are genuinely mandatory.
• Use nullable columns when missing or unknown information is a valid business situation.
• Do not use zero or an empty string to represent missing data unless that is the intended meaning.
• Use IS NULL and IS NOT NULL when filtering nullable columns.
• Check existing records before changing a nullable column to NOT NULL.
• Choose meaningful defaults only when they accurately represent the business rule.
• Use primary keys to identify records uniquely.
• Validate required fields in the application as well as in the database.
• Consider how aggregate functions and reports handle missing values.
• Document why a field is nullable if its purpose may not be obvious.
• Test schema changes in a development environment before applying them to production.
• Avoid replacing unknown information with invented values just to satisfy a constraint.

23. Practical Project: Employee Information Database

In this practical project, create an employee table with required and optional fields, insert sample records, and retrieve records based on nullability.

Step 1: Create the Database

CREATE DATABASE IF NOT EXISTS company_db;
USE company_db;

Step 2: Create the Employees Table

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(20) NULL,
    department VARCHAR(80) NULL,
    salary DECIMAL(10,2) NOT NULL,
    joining_date DATE NOT NULL
);

Step 3: Insert Sample Records

INSERT INTO employees (
    employee_name,
    email,
    phone,
    department,
    salary,
    joining_date
)
VALUES
    ('Rahul Sharma', '[email protected]', '9876543210', 'IT', 45000.00, '2025-01-15'),
    ('Priya Patel', '[email protected]', NULL, 'HR', 42000.00, '2025-02-10'),
    ('Amit Verma', '[email protected]', '9123456780', NULL, 40000.00, '2025-03-05');

Step 4: Find Employees Without Phone Numbers

SELECT *
FROM employees
WHERE phone IS NULL;

Step 5: Find Employees Without a Department

SELECT employee_id, employee_name
FROM employees
WHERE department IS NULL;

Step 6: Count Employees with Phone Numbers

SELECT COUNT(phone) AS employees_with_phone
FROM employees;

Step 7: Display Missing Phone Numbers Clearly

SELECT
    employee_name,
    IFNULL(phone, 'Not provided') AS phone
FROM employees;

Step 8: Verify the Table Structure

DESCRIBE employees;
SHOW CREATE TABLE employees;

Project outcome: You will understand how nullable and non-nullable fields work in a practical employee database and how to find records with missing information.

24. Practice Exercises

1. Create a customers table with a required name and an optional phone number.
2. Create a products table where product name and price cannot be NULL, but description can be NULL.
3. Insert a record with a NULL value into a nullable column.
4. Write a query to find records where the email column is NULL.
5. Write a query to find records where the email column is not NULL.
6. Explain the difference between NULL and an empty string.
7. Count all rows and compare the result with COUNT(phone).
8. Modify a nullable column to NOT NULL after checking the existing data.
9. Use IFNULL() to display a replacement value for missing data.
10. Inspect a table's nullability using DESCRIBE.

25. Interview Questions and Answers

Q1. What does NULL mean in MySQL?

Answer: NULL represents missing, unknown, or unavailable information.

Q2. What is the purpose of NOT NULL?

Answer: It prevents a column from storing NULL values.

Q3. Is NULL the same as zero?

Answer: No. Zero is a numeric value, whereas NULL represents missing or unknown information.

Q4. Is NULL the same as an empty string?

Answer: No. An empty string is a string value containing no characters. NULL represents missing or unknown information.

Q5. How do you find NULL values?

Answer: Use WHERE column_name IS NULL.

Q6. How do you find non-NULL values?

Answer: Use WHERE column_name IS NOT NULL.

Q7. Why does column_name = NULL not work?

Answer: SQL comparisons with NULL produce an unknown result. Use IS NULL instead.

Q8. Can a primary key contain NULL?

Answer: No. Primary-key columns cannot contain NULL values.

Q9. What is the difference between NULL and DEFAULT?

Answer: NULL represents missing or unknown information. DEFAULT specifies the value used when an insert omits a column, subject to the column definition.

Q10. How do you add NOT NULL to an existing column?

Answer: First resolve any existing NULL values, then use ALTER TABLE ... MODIFY COLUMN with the appropriate complete column definition.

Q11. What is the difference between COUNT(*) and COUNT(column_name)?

Answer: COUNT(*) counts all rows, while COUNT(column_name) counts only rows where that column is not NULL.

Q12. What does IFNULL() do?

Answer: It returns an alternative expression when the supplied expression is NULL.

26. Quick Revision Cheat Sheet

27. Official Documentation and Further Learning

• MySQL 8.0 Documentation: NULL Values
• MySQL 8.0 Documentation: Working with NULL Values
• MySQL 8.0 Documentation: CREATE TABLE Statement
• JustAcademy MySQL Training Course
• Register for MySQL Course Demo

28. Conclusion

The NULL and NOT NULL constraints help control whether a MySQL column can contain missing or unknown information. Use NULL when a value may legitimately be unavailable, and use NOT NULL when the information is required. Combine these constraints with suitable data types, defaults, keys, and validation rules to design accurate and dependable databases.

To continue learning database design, explore the JustAcademy MySQL Training Course and register for a course demo.